﻿using System.Data.SQLite;
using System;
using System.IO;
using System.Diagnostics;
using CSharp.Libs.Koc.KernelModels.ItemClasses;
using System.Collections.ObjectModel;

namespace CSharp.Libs.Koc.KernelModels
{
    public partial class DatabaseOperations
    {
        #region declarations

        #region events
        #endregion
        #endregion



        #region event handling
        #endregion



        #region private functions
        /// <summary>_AutoTransportItemExists</summary>
        /// <param name="ServerId"></param>
        /// <param name="CityId"></param>
        /// <returns></returns>
        private Boolean _AutoTransportItemExists(
            Int32 ServerId,
            Int32 CityId)
        {
            try
            {
                String strCMD = String.Concat(
                    "SELECT * FROM tblAutoTransport WHERE ServerID=",
                    ServerId.ToString(),
                    " AND CityID=",
                    CityId.ToString());
                SQLiteCommand CMD = new SQLiteCommand(strCMD, _CON);

                SQLiteDataReader DR = CMD.ExecuteReader(System.Data.CommandBehavior.Default);
                
                if(DR.HasRows)
                {
                    DR.Close();
                    return true;
                }

                DR.Close();
            }
            catch(Exception ex)
            {
                _AB.EC.AddException(ex, this.ToString(), _AB.EC.Tools.GetCurrentMethodName());
            }

            return false;
        }

        /// <summary>_AutoTransportGetItems</summary>
        /// <param name="ServerId"></param>
        /// <param name="CityId"></param>
        /// <returns></returns>
        /// <history>11.04.2011</history>
        private void _AutoTransportGetItems(
           Int32 ServerId,
           ref ObservableCollection<AutoTransportCityItem> ocATCI)
        {
            try
            {
                //Get Item
                String strCMD = "";
                SQLiteCommand CMD = new SQLiteCommand(strCMD, _CON);

                foreach(AutoTransportCityItem ATCI in ocATCI)
                {
                    if(_AutoTransportItemExists(ServerId, ATCI.CityID))
                    {
                        //Get Item
                        strCMD = String.Concat(
                            "SELECT * FROM tblAutoTransport WHERE ServerID=",
                            ServerId.ToString(),
                            " AND CityID=",
                            ATCI.CityID.ToString());
                        CMD.CommandText = strCMD;

                        using(SQLiteDataReader DR = CMD.ExecuteReader(System.Data.CommandBehavior.Default))
                        {

                            if(DR.HasRows)
                            {
                                ATCI.TransportActive = _GetBoolean(DR["TransportActive"]);
                                ATCI.DestinationXCoord = (Int32)_GetInt64(DR["DestinationXCoord"]);
                                ATCI.DestinationYCoord = (Int32)_GetInt64(DR["DestinationYCoord"]);
                                ATCI.TransportGold = _GetBoolean(DR["TransportGold"]);
                                ATCI.TransportFood = _GetBoolean(DR["TransportFood"]);
                                ATCI.TransportWood = _GetBoolean(DR["TransportWood"]);
                                ATCI.TransportStone = _GetBoolean(DR["TransportStone"]);
                                ATCI.TransportOre = _GetBoolean(DR["TransportOre"]);
                                ATCI.MinGold = (Int32)_GetInt64(DR["GoldMin"]);
                                ATCI.MaxGold = (Int32)_GetInt64(DR["GoldMax"]);
                                ATCI.MinFood = (Int32)_GetInt64(DR["FoodMin"]);
                                ATCI.MaxFood = (Int32)_GetInt64(DR["FoodMax"]);
                                ATCI.MinWood = (Int32)_GetInt64(DR["WoodMin"]);
                                ATCI.MaxWood = (Int32)_GetInt64(DR["WoodMax"]);
                                ATCI.MinStone = (Int32)_GetInt64(DR["StoneMin"]);
                                ATCI.MaxStone = (Int32)_GetInt64(DR["StoneMax"]);
                                ATCI.MinOre = (Int32)_GetInt64(DR["OreMin"]);
                                ATCI.MaxOre = (Int32)_GetInt64(DR["OreMax"]);
                            }

                            DR.Close();
                        }
                    }
                    else
                    {
                        //Create Item
                        _AutoTransportCreateItem(ServerId, ATCI);
                    }
                }
            }
            catch(Exception ex)
            {
                _AB.EC.AddException(ex, this.ToString(), _AB.EC.Tools.GetCurrentMethodName());
            }
        }

        /// <summary>_AutoTransportCreateItem
        /// 
        /// </summary>
        /// <param name="ServerId"></param>
        /// <param name="CityId"></param>
        /// <param name="ATCI"></param>
        /// <returns></returns>
        /// <history>11.04.2011</history>
        private Boolean _AutoTransportCreateItem(
            Int32 ServerId,
            AutoTransportCityItem ATCI)
        {
            try
            {
                String strCMD = String.Concat(
                    "INSERT INTO tblAutoTransport(",
                    "ServerID, ",
                    "CityID, ",
                    "TransportActive, ",
                    "DestinationXCoord, ",
                    "DestinationYCoord, ",
                    "TransportGold, ",
                    "TransportFood, ",
                    "TransportWood, ",
                    "TransportStone, ",
                    "TransportOre, ",
                    "GoldMin, ",
                    "GoldMax, ",
                    "FoodMin, ",
                    "FoodMax, ",
                    "WoodMin, ",
                    "WoodMax, ",
                    "StoneMin, ",
                    "StoneMax, ",
                    "OreMin, ",
                    "OreMax) VALUES (",
                    ServerId.ToString(), ", ",
                    ATCI.CityID.ToString(), ", '",
                    _SetBoolean(ATCI.TransportActive).ToString(), "', ",
                    ATCI.DestinationXCoord.ToString(), ", ",
                    ATCI.DestinationYCoord.ToString(), ", '",
                    _SetBoolean(ATCI.TransportGold).ToString(), "', '",
                    _SetBoolean(ATCI.TransportFood).ToString(), "', '",
                    _SetBoolean(ATCI.TransportWood).ToString(), "', '",
                    _SetBoolean(ATCI.TransportStone).ToString(), "', '",
                    _SetBoolean(ATCI.TransportOre).ToString(), "', ",
                    ATCI.MinGold.ToString(), ", ",
                    ATCI.MaxGold.ToString(), ", ",
                    ATCI.MinFood.ToString(), ", ",
                    ATCI.MaxFood.ToString(), ", ",
                    ATCI.MinWood.ToString(), ", ",
                    ATCI.MaxWood.ToString(), ", ",
                    ATCI.MinStone.ToString(), ", ",
                    ATCI.MaxStone.ToString(), ", ",
                    ATCI.MinOre.ToString(), ", ",
                    ATCI.MaxOre.ToString(), ")");
                SQLiteCommand CMD = new SQLiteCommand(strCMD, _CON);
                Int32 Count = CMD.ExecuteNonQuery();
                if (Count > 0)
                {
                    return true;
                }
            }
            catch (Exception ex)
            {
                _AB.EC.AddException(ex, this.ToString(), _AB.EC.Tools.GetCurrentMethodName());
            }

            return false;
        }

        /// <summary>_AutoTransportUpdateItem
        /// 
        /// </summary>
        /// <param name="ServerId"></param>
        /// <param name="CityId"></param>
        /// <param name="ATCI"></param>
        /// <returns></returns>
        /// <history>12.04.2011</history>
        private Boolean _AutoTransportUpdateItem(
            Int32 ServerId,
            AutoTransportCityItem ATCI)
        {
            try
            {
                String strCMD = String.Concat(
                    "UPDATE tblAutoTransport ",
                    "SET ",
                    "TransportActive='", _SetBoolean(ATCI.TransportActive).ToString(), "', ",
                    "DestinationXCoord=", ATCI.DestinationXCoord.ToString(), ", ",
                    "DestinationYCoord=", ATCI.DestinationYCoord.ToString(), ", ",
                    "TransportGold='", _SetBoolean(ATCI.TransportGold).ToString(), "', ",
                    "TransportFood='", _SetBoolean(ATCI.TransportFood).ToString(), "', ",
                    "TransportWood='", _SetBoolean(ATCI.TransportWood).ToString(), "', ",
                    "TransportStone='", _SetBoolean(ATCI.TransportStone).ToString(), "', ",
                    "TransportOre='", _SetBoolean(ATCI.TransportOre).ToString(), "', ",
                    "GoldMin=", ATCI.MinGold.ToString(), ", ",
                    "GoldMax=", ATCI.MaxGold.ToString(), ", ",
                    "FoodMin=", ATCI.MinFood.ToString(), ", ",
                    "FoodMax=", ATCI.MaxFood.ToString(), ", ",
                    "WoodMin=", ATCI.MinWood.ToString(), ", ",
                    "WoodMax=", ATCI.MaxWood.ToString(), ", ",
                    "StoneMin=", ATCI.MinStone.ToString(), ", ",
                    "StoneMax=", ATCI.MaxStone.ToString(), ", ",
                    "OreMin=", ATCI.MinOre.ToString(), ", ",
                    "OreMax=", ATCI.MaxOre.ToString(), " ",
                    "WHERE ServerID=", ServerId.ToString(), " AND CityID=", ATCI.CityID.ToString());

                SQLiteCommand CMD = new SQLiteCommand(strCMD, _CON);
                
                Int32 Count = CMD.ExecuteNonQuery();
                
                if (Count > 0)
                {
                    return true;
                }
            }
            catch (Exception ex)
            {
                _AB.EC.AddException(ex, this.ToString(), _AB.EC.Tools.GetCurrentMethodName());
            }

            return false;
        }

        /// <summary>_AutoTransportUpdateItems
        /// 
        /// </summary>
        /// <param name="Items"></param>
        /// <returns></returns>
        /// <history>13.04.2011</history>
        private Boolean _AutoTransportUpdateItems(
            ObservableCollection<AutoTransportCityItem> Items)
        {
            try
            {
                foreach (AutoTransportCityItem ATCI in Items)
                {
                    if (ATCI.ItemChanged)
                    {
                        if (_AutoTransportUpdateItem(Int32.Parse(_AB.TM.CurrentServerId), ATCI))
                        {
                            ATCI.ItemChanged = false;
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                _AB.EC.AddException(ex, this.ToString(), _AB.EC.Tools.GetCurrentMethodName());
            }

            return false;
        }
        #endregion



        #region interface
        #region properties
        #endregion

        #region methods
        public Boolean AutoTransportItemExists(
            Int32 ServerId,
            Int32 CityId)
        {
            return _AutoTransportItemExists(ServerId, CityId);
        }

        public void AutoTransportGetItems(
           Int32 ServerId,
           ref ObservableCollection<AutoTransportCityItem> ocATCI)
        {
            _AutoTransportGetItems(ServerId, ref ocATCI);
        }

        public Boolean AutoTransportCreateItem(
            Int32 ServerId,
            AutoTransportCityItem ATCI)
        {
            return _AutoTransportCreateItem(ServerId, ATCI);
        }

        public Boolean AutoTransportUpdateItems(
            ObservableCollection<AutoTransportCityItem> Items)
        {
            return _AutoTransportUpdateItems(Items);
        }
        #endregion
        #endregion
    }
}
